package com.gugee.tiktok.common.model.dal.blogger;

import com.gugee.tiktok.common.model.mysql.BloggerRecord;
import com.gugee.tiktok.common.model.ranking.BloggerGrowth;
import org.springframework.data.jdbc.repository.query.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

@Repository
public interface BloggerRecordRepository extends CrudRepository<BloggerRecord,Long> {

    @Query("SELECT * FROM blogger_record where datecode = :dateCode AND follower_count != follower_growth Order by follower_growth desc limit 0,1000")
    List<BloggerRecord> findTop1000ByDatecodeOrderByFollowerGrowthDesc(int dateCode);

    List<BloggerRecord> findTop1000ByDatecodeAndBloggerTypeOrderByFollowerGrowthDesc(int dateCode,String BloggerType);

    Optional<BloggerRecord> findByUidAndDatecode(long uid,int dateCode);

    Optional<BloggerRecord> findTop1ByUidOrderByDatecodeDesc(long uid);

    @Query("SELECT " +
            "IFNULL(ROUND(SUM(follower_growth)),0) follower_growth, IFNULL(ROUND(SUM(like_total_count_growth)),0) like_growth,IFNULL(ROUND(SUM(share_growth)),0) share_growth," +
            "IFNULL(ROUND(SUM(review_growth)),0) review_growth,IFNULL(ROUND(SUM(follower_growth)),0) follower_count,IFNULL(ROUND(SUM(view_growth)),0) view_growth," +
            "IFNULL(ROUND(AVG(avg_view)),0) avg_view " +
            "FROM blogger_record where uid = :uid and datecode BETWEEN :startDateCode AND :endDateCode")
    BloggerGrowth findByGrowthData(long uid, int startDateCode, int endDateCode);

}
